package com.zyy.dao.user.impl;

import com.mysql.jdbc.StringUtils;
import com.zyy.dao.BaseDao;
import com.zyy.dao.user.UserDao;
import com.zyy.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName: UserDaoImpl
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2022/01/09 10:51
 * @Version: 1.0
 */
public class UserDaoImpl implements UserDao {
    @Override
    public User getLoginUser(Connection con, String userCode) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        if (con != null) {
            String sql = "select id, userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy, creationDate, modifyBy, modifyDate from smbms_user where userCode=?";
            Object[] params = {userCode};
            rs = BaseDao.execute(con, ps, rs, sql, params);
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setCreationDate(rs.getTimestamp("creationDate"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            //关闭
            BaseDao.closeResource(ps, rs);
        }
        return user;
    }

    @Override
    public int updatePwd(Connection con, int id, String password) throws SQLException {
        int updateCount = 0;
        if (con != null) {
            PreparedStatement ps = null;
            String str = "update smbms_user set userPassword = ? where id = ?";
            Object[] params = {password, id};
            updateCount = BaseDao.execute(con, ps, str, params);
            BaseDao.closeResource(ps, null);
        }
        return updateCount;
    }

    @Override
    public int getUserCount(Connection con, String userName, int userRole) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        if (con != null) {
            StringBuffer sql = new StringBuffer("select count(1) as 'count' from smbms_user u,smbms_role r where u.userRole=r.id");
            List<Object> list = new ArrayList<Object>();
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }
            if (userRole > 0) {
                sql.append(" and r.id = ?");
                list.add(userRole);
            }
            Object[] params = list.toArray();
            rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
            if (rs.next()) {
                count = rs.getInt("count");
            }
            BaseDao.closeResource(ps, rs);
        }
        return count;
    }

    @Override
    public List<User> getUserList(Connection con, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<User>();
        if (con != null) {
            StringBuffer sql = new StringBuffer("select u.*,r.roleName as `userRoleName` from smbms_user u,smbms_role r where u.userRole=r.id");
            List<Object> list = new ArrayList<Object>();
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }
            if (userRole > 0) {
                sql.append(" and r.id = ?");
                list.add(userRole);
            }
            //mysql 分页使用limit startIndex, pageSize
            //比如现在一共13条数据，每页最大容量是5
            //0,5 01234 第一页
            //5,5 56789 第二页
            //10,3 10,11,12 第三页
            sql.append(" order by u.creationDate desc limit ?,?");
            currentPageNo = (currentPageNo - 1) * pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setUserRole(rs.getInt("userRole"));
                user.setUserRoleName(rs.getString("userRoleName"));
                userList.add(user);
            }
            BaseDao.closeResource(ps, rs);

        }
        return userList;
    }


    @Override
    public int addUser(Connection con, User user) throws SQLException {
        PreparedStatement ps = null;
        int count = 0;
        if (con != null) {
            String currentDateTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            String str = "insert into smbms_user(userCode, userName, userPassword, gender, birthday, phone, address, userRole, creationDate, modifyDate) values (?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {user.getUserCode(), user.getUserName(), user.getUserPassword(), user.getGender(), user.getBirthday()
                    , user.getPhone(), user.getAddress(), user.getUserRole(), currentDateTime, currentDateTime};
            count = BaseDao.execute(con, ps, str, params);
        }
        BaseDao.closeResource(ps, null);

        return count;

    }
}
